{$A8,B-,C+,D+,E-,F-,G+,H+,I+,J-,K-,L+,M-,N+,O+,P+,Q-,R-,S-,T-,U-,V+,W-,X+,Y+,Z1}
{$WARN UNSAFE_TYPE OFF}

unit SQLResultInterface;

interface

  uses
    Interface_TLB, ComObj, SQLiteWrap, Classes;

  type
    TSQLResult = class(TAutoIntfObject, ISQLResultIntf, IDispatch)
      private
        fTable : TSqliteTable;
        fColumns : TStringList;
        fColumnData : Array of TStringList;
        fColumnCount : Integer;
        fRowCount : Cardinal;
        fErrorMessage : Widestring;
        procedure SetupResult();
        function ColumnIndex(ColumnName : WideString; var IsValid : Boolean) : Cardinal;
      protected
        function Column(index: Integer): WideString; safecall;
        function Result(const field: WideString; row: Cardinal): OleVariant; safecall;
        function Get_columnCount: Integer; safecall;
        function Get_rowCount: Cardinal; safecall;
        function Get_Error: WideString; safecall;
        property columnCount: Integer read Get_columnCount;
        property rowCount: Cardinal read Get_rowCount;
        property Error: WideString read Get_Error;
      public
        constructor Create(Table : TSqliteTable; ErrorMessage : Widestring);
        destructor Destroy(); override;
    end;

implementation

  uses
    ActiveX,
    Variants,
    SysUtils,
    StringUtils,
    Dialogs,
    Windows;

  constructor TSQLResult.Create(Table : TSqliteTable; ErrorMessage : Widestring);
  var
    TypeLib : ITypeLib;
    ExeName : Widestring;
  begin
    //Get name of application
    ExeName := ParamStr(0);
    //Load type library from application's resources
    OleCheck(LoadTypeLib(PWideChar(ExeName), TypeLib));
    //Inherit
    inherited Create(TypeLib, ISQLResultIntf);
    //Set parameters
    fTable := Table;
    fErrorMessage := ErrorMessage;
    //Setup result
    SetupResult();
  end;

  destructor TSQLResult.Destroy();
  begin
    inherited;
  end;

  //****************************************************************************
  // dsf
  //****************************************************************************
  procedure TSQLResult.SetupResult();
  var
    i : Cardinal;
  begin
    if (fTable <> nil) and (fTable.ColCount > 0) then
    begin
      //Setup string lists
      fColumns := TStringList.Create();
      SetLength(fColumnData, fTable.ColCount);
      for i := 0 to fTable.ColCount - 1 do
      begin
        //Save column name
        fColumns.Add(Utf8ToWidestring(fTable.Columns[i]));
        //Create string list to hold column data
        fColumnData[i] := TStringList.Create();
      end;
      //Enumerate data
      while not fTable.EOF do
      begin
        //Grab each field
        for i := 0 to fTable.ColCount - 1 do
          fColumnData[i].Add(Utf8ToWidestring(fTable.FieldAsString(i)));
        fTable.Next();
      end;
      //Set counts
      fColumnCount := fTable.ColCount;
      fRowCount := fTable.Row;
    end;
    //Get rid of table
    fTable.Free();
  end;

  function TSQLResult.ColumnIndex(ColumnName: WideString; var IsValid: Boolean) : Cardinal;
  var
    i : Cardinal;
  begin
    IsValid := false;
    Result := 0;
    for i := 0 to fColumnCount - 1 do
      if UpperCase(ColumnName) = fColumns.Strings[i] then
      begin
        Result := i;
        IsValid := true;
        break;
      end;
  end;

  //****************************************************************************
  // Methods
  //****************************************************************************
  {
    Column
    Retrieve column name by index
  }
  function TSQLResult.Column(index : Integer) : Widestring;
  begin
    Result := '';
    if (index < fColumns.Count) and (fColumns.Count > 0) then
      Result := fColumns.Strings[index];
  end;

  {
    Result
    Retrieve data by field name and row
    Todo: Waste of code, look for workaround
  }
  function TSQLResult.Result(const field: WideString; row: Cardinal) : OleVariant;
  var
    ColumnIndex : Integer;
    IsValid : Boolean;
  begin
    Result := VarAsType(Result, varEmpty);
    if (row <= fRowCount) and (row > 0) then
    begin
      try
        ColumnIndex := self.ColumnIndex(field, IsValid);
        if IsValid then
          Result := fColumnData[ColumnIndex].Strings[row - 1];
      finally
        //fTable.Reset();
      end;
    end;
  end;

  //****************************************************************************
  // Properties
  //****************************************************************************
  {
    Get_ColumnCount
  }
  function TSQLResult.Get_columnCount() : Integer;
  begin
    Result := fColumnCount;
  end;

  {
    Get_RowCount
  }
  function TSQLResult.Get_rowCount() : Cardinal;
  begin
    Result := fRowCount;
  end;

  {
    Get_Error
  }
  function TSQLResult.Get_Error() : Widestring;
  begin
    Result := fErrorMessage;
  end;

end.
